import pymysql

from app import db
from app.mappers.models import EmployeeInfo, DepartmentInfo, PositionInfo, RoleInfo


class EmployeeInfoDao():
    def __init__(self):
        print("EmployeeInfoDao is running")  # 类初始化时输出提示信息

    # def findByUsername(self, username, password):
    #     try:
    #         employeeInfo = EmployeeInfo.query.filter_by(
    #             username=username, password=password).first()
    #         return employeeInfo
    #     except Exception as e:
    #         print(e)
    #         pass
    def findByUsername(self, username, password):
        emp = EmployeeInfo
        dep = DepartmentInfo
        pos = PositionInfo
        rol = RoleInfo

        query = db.session.query(emp.id,
                                 emp.name.label('emp_name'),
                                 emp.username,
                                 emp.password,
                                 emp.gender,
                                 dep.name.label('dep_name'),
                                 pos.name.label('pos_name'),
                                 emp.contact_number,
                                 rol.NAME.label('rol_name'),
                                 emp.photo). \
            join(dep, emp.department_id == dep.id). \
            join(pos, emp.position_id == pos.id). \
            join(rol, emp.role_id == rol.id). \
            where(emp.username == username,
                  emp.password == password)

        emp_list = []

        # 使用游标迭代结果集
        for emp_tuple in query.yield_per(100):
            emp_dict = {
                "id": emp_tuple.id,
                "name": emp_tuple.emp_name,
                "username": emp_tuple.username,
                "password": emp_tuple.password,
                "gender": emp_tuple.gender,
                "department_name": emp_tuple.dep_name,
                "position_name": emp_tuple.pos_name,
                "contact_number": emp_tuple.contact_number,
                "role_name": emp_tuple.rol_name,
                "photo": emp_tuple.photo,
            }
            emp_list.append(emp_dict)

        return emp_list

    def findAll(self):
        try:
            employeeInfo = EmployeeInfo.query.all()  # 查询所有员工信息记录
            return employeeInfo
        except Exception as e:
            print(e)
            pass

    def addOne(self, employeeInfo):
        flag = True
        try:
            db.session.add(employeeInfo)  # 添加一条员工信息记录
            db.session.commit()
        except Exception as e:
            print(e)
            flag = False
            db.session.rollback()
            pass
        return flag

    def updateById(self, id, username, password, name, gender, department_id, position_id, contact_number, photo):
        flag = True
        try:
            employeeInfo = EmployeeInfo.query.get(id)  # 根据员工编号获取员工信息记录实例
            employeeInfo.username = username
            employeeInfo.password = password
            employeeInfo.name = name
            employeeInfo.gender = gender
            employeeInfo.department_id = department_id
            employeeInfo.position_id = position_id
            employeeInfo.contact_number = contact_number
            employeeInfo.photo = photo
            db.session.commit()  # 提交事务
        except Exception as e:
            print(e)
            flag = False
            db.session.rollback()
        return flag

    def deleteById(self, id):
        flag = True
        try:
            employeeInfo = EmployeeInfo.query.get(id)  # 根据员工编号获取员工信息记录实例
            if employeeInfo is not None:
                db.session.delete(employeeInfo)  # 删除该实例对应的员工信息记录
                db.session.commit()  # 提交事务
            else:
                flag = False
        except Exception as e:
            print(e)
            flag = False
            db.session.rollback()
        return flag

    def getById(self, id):
        emp = EmployeeInfo
        dep = DepartmentInfo
        pos = PositionInfo
        rol = RoleInfo

        query = db.session.query(emp.id,
                                 emp.name.label('emp_name'),
                                 emp.username,
                                 emp.password,
                                 emp.gender,
                                 dep.name.label('dep_name'),
                                 pos.name.label('pos_name'),
                                 emp.contact_number,
                                 rol.NAME.label('rol_name'),
                                 emp.photo). \
            join(dep, emp.department_id == dep.id). \
            join(pos, emp.position_id == pos.id). \
            join(rol, emp.role_id == rol.id). \
            where(emp.id == id)

        emp_list = []

        # 使用游标迭代结果集
        for emp_tuple in query.yield_per(100):
            emp_dict = {
                "id": emp_tuple.id,
                "name": emp_tuple.emp_name,
                "username": emp_tuple.username,
                "password": emp_tuple.password,
                "gender": emp_tuple.gender,
                "department_name": emp_tuple.dep_name,
                "position_name": emp_tuple.pos_name,
                "contact_number": emp_tuple.contact_number,
                "role_name": emp_tuple.rol_name,
                "photo": emp_tuple.photo,
            }
            emp_list.append(emp_dict)

        return emp_list

    # 根据部门名称和员工姓名查询部门和员工信息
    # def get_all_by_name_or_department(self, employee_name, department_name):
    #     try:
    #         query = db.session.query(DepartmentInfo, EmployeeInfo).join(EmployeeInfo,
    #                                                                     DepartmentInfo.id == EmployeeInfo.department_id)
    #         # 使用filter_by()方法动态构建查询条件
    #         if department_name is not None:
    #             query = query.filter(DepartmentInfo.name.like(f"%{department_name}%"))
    #         if employee_name is not None:
    #             query = query.filter(EmployeeInfo.name.contains(employee_name))
    #
    #         result = query.distinct().all()
    #         return result
    #
    #     except Exception as e:
    #         print(e)
    #         pass

    def get_by_conditional(self, name, department_id, position_id):
        try:
            query = db.session.query(EmployeeInfo.name,
                                     EmployeeInfo.gender,
                                     DepartmentInfo.name,
                                     PositionInfo.name,
                                     EmployeeInfo.contact_number). \
                join(DepartmentInfo, EmployeeInfo.department_id == DepartmentInfo.id). \
                join(PositionInfo, EmployeeInfo.position_id == PositionInfo.id)

            if name is not None or name == "":
                query = query.filter(EmployeeInfo.name == name)
            if department_id is not None or department_id == "":
                query = query.filter(DepartmentInfo.id == department_id)
            if position_id is not None or position_id == "":
                query = query.filter(PositionInfo.id == position_id)

            query_result = query.all()

            # 将查询结果转换成由字典组成的列表
            employee_list = []
            for name, gender, department_name, position_name, contact_number in query_result:
                employee_info_dict = {
                    "name": name,
                    "gender": gender,
                    "department_name": department_name,
                    "position_name": position_name,
                    "contact_number": contact_number
                }
                employee_list.append(employee_info_dict)

            return employee_list
        except Exception as e:
            print(e)
            pass
